/***************************************************************************************************
 The contents of this file are subject to the Mozilla Public License Version 1.1 (the "License"); 
 you may not use this file except in compliance with the License. You may obtain a copy of the    
 License at http://www.mozilla.org/MPL/                                                           
                                                                                                  
 Software distributed under the License is distributed on an "AS IS" basis, WITHOUT WARRANTY OF   
 ANY KIND, either express or implied. See the License for the specific language governing rights  
 and limitations under the License.                                                               
                                                                                                  
 The Original Code is PoiDAO.cs                                                             
                                                                                                  
 The Initial Developer of the Original Code is Fabrizio Vita (fabrizio.vita@itacasoft.com)        
***************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using TkSilverlight.DAO.Entities;
using TkSilverlight.DAL;
using System.Globalization;

namespace TkSilverlight.DAO
{
    /// <summary>
    /// This class managees a table of POIs. The structure is as follow:
    ///  
    ///  ID (guid)
    ///  NAME (varchar)
    ///  DESCRIPTION (longtext)
    ///  ASSIGNED_USER_ID (guid)
    ///  CATEGORY (guid)
    ///  SUBCATEGORY  (guid)
    ///  LATITUDE  (float)
    ///  LONGITUDE (float)
    /// </summary>
    public class PoiDAO: SimpleTableDao
    {
        public PoiDAO()
            :base()
        {
                _value_fieldname = "DESCRIPTION";
                _tablename = "POI";
        }

        string QuoteText(string text)
        {
            text = text.Replace("'", "''");
            return "'" + text + "'"; 
        }

        protected int Update(Guid id, Guid userid, POI poi)
        {
            BeginTransaction();
            int result = 0;

            try
            {
                try
                {
                    string sql = "update " + _tablename
                    + " set " + _name_fieldname + " = " + QuoteText(poi.Name)
                    + ",ASSIGNED_USER_ID = '" + userid.ToString() + "'"
                    + "," + _value_fieldname + " = " + QuoteText(poi.Description)
                    + ", CATEGORY = '" + poi.CategoryID.ToString() + "'"
                    + ", SUBCATEGORY = '" + poi.SubCategoryID.ToString() + "'"
                    + ", latitude = " + Convert.ToString(poi.Latitude, CultureInfo.InvariantCulture)
                    + ", longitude = " + Convert.ToString(poi.Longitude, CultureInfo.InvariantCulture)
                    + " where " + _id_fieldname + " = " + id.ToString();
                    
                    result = ExecuteSQL(sql);

                    CommitTransaction();
                    return result;
                }
                catch(Exception ex)
                {
                    RollbackTransaction();
                    throw ex;
                }      
            }
            finally
            {
            }
        }

        protected int Insert(Guid userid, POI poi, out Guid id)
        {
            try
            {
                BeginTransaction();
                int result = 0;
                id = Guid.NewGuid();

                try
                {
                    string sql = "insert into " + _tablename
                    + " (" + _id_fieldname
                    + ", ASSIGNED_USER_ID"
                    + ", " + _value_fieldname
                    + ", " + _name_fieldname
                    + ", category"
                    + ", subcategory"
                    + ", latitude"
                    + ", longitude"
                    + ") values "
                    + "('" + id + "'"
                    + ",'" + userid.ToString() + "'"
                    + "," + QuoteText(poi.Description)
                    + "," + QuoteText(poi.Name)
                    + ",'" + poi.CategoryID.ToString() + "'"
                    + ",'" + poi.SubCategoryID.ToString() + "'"
                    + "," + Convert.ToString(poi.Latitude, CultureInfo.InvariantCulture)
                    + "," + Convert.ToString(poi.Longitude, CultureInfo.InvariantCulture)
                    + ")";

                    result = result + ExecuteSQL(sql);

                    CommitTransaction();
                    return result;
                }
                catch(Exception ex)
                {
                    RollbackTransaction();
                    throw;
                }
            }
            finally
            {
            }
        }

        protected bool RecordExists(Guid userid, string name, out Guid id)
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "select " + _id_fieldname + " from " + _tablename + " where " + _name_fieldname + " = " + QuoteText(name)
                    + " and ASSIGNED_USER_ID = '" + userid.ToString() + "'";

                using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (rdr.Read())
                    {
                        id = Sql.ToGuid(rdr[0]);
                        return true;
                    }
                    else
                    {
                        id = Guid.Empty;
                        return false;
                    }
                }
            }
        }


        public int Save(Guid userid, POI poi)
        {
            Guid id;
            int result;

            if (RecordExists(userid, poi.Name, out id))
            {
                result = Update(id, userid, poi);
            }
            else
            {
                result = Insert(userid, poi, out id);
            }

            poi.ID = id;
            return result;
        }


        public string ListAsXML(string userid, string category)
        {
            string sql = "select " + _id_fieldname +
            ",ASSIGNED_USER_ID" +
            "," + _name_fieldname +
            "," + _value_fieldname +
            ",category" +
            ",subcategory" +
            ",latitude" +
            ",longitude" +
            " from " + _tablename +
            " where category = '" + category.ToString() + "'" +
            " and ASSIGNED_USER_ID = '" + userid.ToString() + "'" + 
            " order by " + _name_fieldname;

            List<POI> ls = new List<POI>();

            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = sql;
                using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.Default))
                {
                    while (rdr.Read())
                    {
                        POI item = new POI();
                        item.ID = Sql.ToGuid(rdr[0]);
                        item.Name = Sql.ToString(rdr[2]);
                        item.Description = Sql.ToString(rdr[3]);
                        item.CategoryID = Sql.ToGuid(rdr[4]);
                        item.SubCategoryID = Sql.ToGuid(rdr[5]);
                        item.Latitude = Sql.ToDouble(rdr[6]);
                        item.Longitude = Sql.ToDouble(rdr[7]);
                        ls.Add(item);
                    }
                }
            }

            return ObjectXMLSerializer<List<POI>>.SerializeObject(ls);
        }
    }
}

